package com.exonline.commons.utils;

import javax.servlet.http.HttpServletRequest;
/**
 *sql生成工具类
 * @author YUKI
 * @date 2024/11/29
 */
public class SqlConditionBuilder {
    private SqlConditionBuilder() {
    }

    /**
     * 针对课程筛选的工具方法 生成sql语句的WHERE 后的条件部分
     * condition参数值: 第一位t上架/f下架/A全部  第二位f免费/p付费/A全部
     * 示例: aa 全部课程 tf 上架状态的免费课程
     * name参数值,可选,带有此参数时,根据名称精确查询
     *
     * @author YUKI
     * @date 2024/11/28
     */

    public static String buildCourseCondition(HttpServletRequest request) {
        // 根据 request 参数生成 SQL 条件
        StringBuilder sqlCondition = new StringBuilder();

        // 获取 condition 参数

        String condition = request.getParameter("condition");
        if (condition != null && condition.length() >= 2) {
            // 第一个参数 (t/f) 判断 cstatus
            char statusChar = condition.charAt(0);
            if (statusChar == 't') {
                sqlCondition.append("cstatus = '1'");
            } else if (statusChar == 'f') {
                sqlCondition.append("cstatus = '0'");
            } else if (statusChar == 'A') {
                sqlCondition.append("1=1");
            } else if (statusChar == 'e') {
                sqlCondition.append("cstatus = '2'");
            }

            // 第二个参数 (f/p) 判断 cprice
            char priceChar = condition.charAt(1);
            if (priceChar == 'f') {
                sqlCondition.append(" AND cprice  LIKE '0%'");
            } else if (priceChar == 'p') {
                sqlCondition.append(" AND cprice NOT LIKE '0%'");
            } else if (priceChar == 'A') {
                sqlCondition.append(" AND 1=1");
            }
        }

        // 检查是否有 name 参数
        String name = request.getParameter("cname");
        if (name != null && !name.isEmpty()) {
            if (sqlCondition.length() > 0) {
                sqlCondition.append(" AND ");
            }
            sqlCondition.append("cname like '").append(name+"%").append("'");
        }

        // 返回最终生成的 SQL 条件部分
        return sqlCondition.toString();
    }


    /**
     *  根据条件生成 WHERE 条件部分的sql语句
     *  condition参数值:  第一位:0活跃/1封号
     *  不太好用,建议使用 生成全部sql语句的方法
     * @author YUKI
     * @date 2024/11/29
     */
    public static String buildUserCondition(HttpServletRequest request) {
        StringBuilder sqlCondition = new StringBuilder();

        // 获取 condition 参数

        String condition = request.getParameter("condition");
        if (condition != null && condition.length() >= 2) {
            // 第一个参数 (t/f) 判断 status
            char statusChar = condition.charAt(0);
            if (statusChar == 't') {
                sqlCondition.append("status = '1'");
            } else if (statusChar == 'f') {
                sqlCondition.append("status = '0'");
            } else if (statusChar == 'a') {
                sqlCondition.append("1=1");
            }

        }

        // 检查是否有 name 参数
        String name = request.getParameter("name");
        if (name != null && !name.isEmpty()) {
            if (sqlCondition.length() > 0) {
                sqlCondition.append(" AND ");
            }
            sqlCondition.append("cname = '").append(name).append("'");
        }
        // 返回最终生成的 SQL 条件部分
        return sqlCondition.toString();
    }

    /**
     * 根据条件生成全部sql语句
     * condition参数值:  第一位:t活跃/f封号   第二位:角色身份,t老师/s学生/a管理员
     * 示例: ts 账号处于活跃状态的学生
     *
     * @author YUKI
     * @date 2024/11/29
     */
    public static String buildUserQueryFullSqlCondition(HttpServletRequest request) {

        StringBuilder sqlCondition = new StringBuilder();

        // 获取 condition 参数
        String condition = request.getParameter("condition");
        if (condition != null && condition.length() >= 2) {
            // 第二个参数 t老师/s学生/a管理员
            char roleChar = condition.charAt(1);
            if (roleChar == 't') {
                sqlCondition.append(" SELECT t.id,tname as name,workyears,direction,introduce,email,phone,password,status,registrationtime,lastlogintime FROM ");
                sqlCondition.append("teacher t");
                sqlCondition.append("  JOIN ");
                sqlCondition.append("userrole u ON t.id = u.tid");

            } else if (roleChar == 's') {
                sqlCondition.append(" SELECT * FROM ");
                sqlCondition.append("student s");
                sqlCondition.append(" JOIN ");
                sqlCondition.append("userrole u ON s.id = u.sid");
            } else if (roleChar == 'a') {
                sqlCondition.append(" SELECT * FROM ");
                sqlCondition.append("admin a");
                sqlCondition.append(" JOIN ");
                sqlCondition.append("userrole u ON a.id = u.aid ");
            }

//            //右连接用户角色表和用户表
//            sqlCondition.append(" RIGHT JOIN ");
//            char userroleChar = condition.charAt(1);
//            if (userroleChar == 't') {
//                sqlCondition.append("userrole u ON t.id = u.tid");
//            } else if (userroleChar == 's') {
//                sqlCondition.append("userrole u ON s.id = u.sid");
//            } else if (userroleChar == 'a') {
//                sqlCondition.append("userrole u ON a.id = u.aid ");
//            }
//            //右边连接
            sqlCondition.append(" JOIN role r ON r.id = u.rid");


            sqlCondition.append(" WHERE ");
            // 第一个参数 (t/f) 判断 status
            char statusChar = condition.charAt(0);
            if (statusChar == 't') {
                sqlCondition.append("status = '1'");
            } else if (statusChar == 'f') {
                sqlCondition.append("status = '0'");
            } else if (statusChar == 'A') {
                sqlCondition.append("1=1");
            }

        }

        // 检查是否有 name 参数
        String name = request.getParameter("name");
        if (name != null && !name.isEmpty()) {
            if (sqlCondition.length() > 0) {
                sqlCondition.append(" AND ");
            }
            sqlCondition.append("cname = '").append(name).append("'");
        }
//        // 第一个参数 (t/f) 判断 status
//        char roleChar = condition.charAt(1);
//        if (roleChar == 't') {
//            sqlCondition.append("userrole u ON t.tid = u.tid");
//        } else if (roleChar == 's') {
//            sqlCondition.append("userrole u ON s.sid = u.sid");
//        } else if (roleChar == 'a') {
//            sqlCondition.append("userrole u ON a.id = u.aid ");
//        }

        // 返回最终生成的 SQL 条件部分
        return sqlCondition.toString();
    }


}


